# vim dws_to_ads.sh


#!/bin/bash

APP=online_edu
# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$2" ] ;then
    do_date=$2
else 
    do_date=date -d "-1 day" +%F
fi


ads_traffic_stats_by_channel="
insert overwrite table ${APP}.ads_traffic_stats_by_channel
select * from ${APP}.ads_traffic_stats_by_channel
union
select
    '$do_date',
    recent_days,
    channel,
    cast(count(distinct mid_id) as bigint),
    cast(avg(during_time_1d) as bigint),
    cast(avg(page_count_1d) as bigint),
    cast(count(*) as bigint),
    cast(sum(if(page_count_1d=1,1,0))/count(*) as decimal(16,2))
from ${APP}.dws_traffic_session_page_view_1d
lateral view explode(array(1,7,30)) tmp as recent_days
where dt > date_sub('$do_date',recent_days)
group by recent_days,channel;
"

ads_page_path="
insert overwrite table ${APP}.ads_page_path
select * from ${APP}.ads_page_path
union
select
    '$do_date',
    source,
    nvl(target,'null'),
    count(*)
from(
    select
        concat('step-',rn,page_id) source,
        concat('step-',rn+1,next_page_id) target
    from(
        select
            page_id,
            lead(page_id,1,null) over(partition by session_id order by view_time) next_page_id,
            row_number() over(partition by session_id order by view_time) rn
        from ${APP}.dwd_traffic_page_view_inc
        where dt = '$do_date' and page_id is not null
    )t1
)t2
group by source,target;
"


ads_order_by_channel="
insert overwrite table ${APP}.ads_order_by_channel
select * from ${APP}.ads_order_by_channel
union
select
    '$do_date',
    t1.recent_days,
    payment_type,
    all_amount,
    cast((channel_cnt/user_cnt) as decimal(16,2)) rate
from(
    select
        '$do_date',
        recent_days,
        payment_type,
        cast(sum(total_amount) as decimal(16,2)) all_amount,
        count(*) channel_cnt
    from ${APP}.dwd_trade_pay_detail_suc_inc 
    lateral view explode(array(1,7,30)) tmp as recent_days
    where dt > date_sub('$do_date',recent_days)
    group by recent_days,payment_type
)t1 
join (
    select
        recent_days,
        count(*) user_cnt
    from ${APP}.dwd_trade_pay_detail_suc_inc
    lateral view explode(array(1,7,30)) tmp as recent_days
    where dt > date_sub('$do_date',recent_days)
    group by recent_days
)t2 on t1.recent_days = t2.recent_days;
"

ads_user_change="
insert overwrite table ${APP}.ads_user_change
select * from ${APP}.ads_user_change
union 
select
    churn.dt,
    user_churn_count,
    user_back_count
from(
    select
        '$do_date' dt,
        count(*) user_churn_count
    from ${APP}.dws_user_user_login_td
    where dt='$do_date' and login_date_last < date_sub('$do_date',7)
)churn
join
(
    select
        '$do_date' dt,
        count(*) user_back_count
    from(
        select
            user_id
        from ${APP}.dws_user_user_login_td
        where dt = '$do_date' and login_date_last = '$do_date'
    )t1
    join(
        select
            user_id
        from ${APP}.dws_user_user_login_td
        where dt = date_sub('$do_date',1) 
        and login_date_last < date_sub('$do_date',8)
    )t2
    on t1.user_id = t2.user_id
)back    
on churn.dt = back.dt;
"


ads_user_retention="
insert overwrite table ${APP}.ads_user_retention
select * from ${APP}.ads_user_retention
union 
select
    '$do_date',
    login_first,
    datediff('$do_date',login_first),
    sum(if(login_date_last='$do_date',1,0)) recent_count,
    count(*) new_user_count,
    cast(sum(if(login_date_last='$do_date',1,0))/count(*)*100 as decimal(16,2)) rate
from
( 
    select
        user_id,
        date_id login_first
    from ${APP}.dwd_user_register_inc
    where dt > date_sub('$do_date',8) and dt < '$do_date'
)t1
join(
    select
        user_id,
        login_date_last
    from ${APP}.dws_user_user_login_td
    where dt = '$do_date'
)t2
on t1.user_id = t2.user_id
group by login_first;
"

ads_user_count="
insert overwrite table ${APP}.ads_user_count
select * from ${APP}.ads_user_count
union
select
    '$do_date' dt,
     t1.recent_days,
     new_user_count,
     active_user_count
from
(
    select
      recent_days,
       sum(if(login_date_last>=date_add('$do_date',-recent_days+1),1,0)) new_user_count
     from ${APP}.dws_user_user_login_td lateral view explode(array(1,7,30)) tmp as recent_days
    where dt='$do_date'
    group by recent_days
 )t1
join
    (
    select
        recent_days,
        sum(if(date_id>=date_add('$do_date',-recent_days+1),1,0)) active_user_count
    from ${APP}.dwd_user_register_inc lateral view explode(array(1,7,30)) tmp as recent_days
    group by recent_days
)t2
on t1.recent_days=t2.recent_days;
"

ads_user_action="
insert overwrite table ${APP}.ads_user_action
select * from ${APP}.ads_user_action
union
select
    '$do_date' dt,
    home_count,
    detail_count,
    cart_count,
    order_count,
    payment_count
from
(
    select
        1 recent_days,
        sum(if(page_id='home',1,0)) home_count,
        sum(if(page_id='course_detail',1,0)) detail_count
    from ${APP}.dwd_traffic_page_view_inc
    where dt='$do_date'
    and page_id in ('home','course_detail')
)page
join
(
    select
        1 recent_days,
        count(*) cart_count
    from ${APP}.dwd_trade_cart_add_inc
    where dt='$do_date'
)cart
on page.recent_days=cart.recent_days
join
(
    select
        1 recent_days,
        count(*) order_count
    from ${APP}.dwd_trade_order_detail_inc
    where dt='$do_date'
)ord
on page.recent_days=ord.recent_days
join
(
    select
        1 recent_days,
        count(*) payment_count
    from ${APP}.dwd_trade_pay_detail_suc_inc
    where dt='$do_date'
)pay
on page.recent_days=pay.recent_days;
"

ads_new_order_user_stats="
insert overwrite table ${APP}.ads_new_order_user_stats
select * from ${APP}.ads_new_order_user_stats
union
select
    '$do_date' dt,
     t1.recent_days,
    new_order_user_count,
    new_pay_user_count
from (
select
    '$do_date' dt,
    recent_days,
    sum(if(order_date_first>=date_add('$do_date',-recent_days+1),1,0)) new_order_user_count
from ${APP}.dws_user_new_user_order_td lateral view explode(array(1,7,30)) tmp as recent_days
where dt = '$do_date'
group by tmp.recent_days)t1
join
(
select
       '$do_date' dt,
       recent_days,
    sum(if(data_id >=date_add('$do_date',-recent_days+1),1,0)) new_pay_user_count
from ${APP}.dwd_trade_order_detail_inc lateral view explode(array(1,7,30)) tm as recent_days
where dt = '$do_date'
group by tm.recent_days)t2
on t1.recent_days = t2.recent_days;
"

ads_age_state_order="
insert overwrite table ${APP}.ads_age_state_order
select *
from ${APP}.ads_age_state_order
union
select '$do_date' dt,
       cast(recent_days as BIGINT),
       age_state,
       count(distinct (if(data_id >= date_sub('$do_date', recent_days), user_id, null)))
from (
         select user_id,
                data_id,
                case
                    when age < 20 then '[0,20)'
                    when age >= 20 and age < 40 then '[20,40)'
                    when age >= 40 and age < 60 then '[40,60)'
                    when age >= 60 and age < 80 then '[60,80)'
                    when age >= 80 then '[80, +)'
                    end age_state
         from (
                  select user_id,
                         data_id,
                         cast((datediff(current_date(), birthday) / 365) as BIGINT) age
                  from (
                           select user_id, birthday, data_id
                           from (
                                    select user_id,
                                           data_id
                                    from ${APP}.dwd_trade_order_detail_inc
                                    where dt > date_sub('$do_date', 30)
                                ) t1
                                    left join
                                (
                                    select id, birthday
                                    from ${APP}.dim_user_zip
                                    where dt = '$do_date'
                                ) t2 on t1.user_id = t2.id
                       ) t3
              ) t4
     ) t5
         lateral view explode(array(1, 7, 30)) tmp as recent_days
group by recent_days, age_state;
"

ads_category_order_trade="
insert overwrite table ${APP}.ads_category_order_trade
select *
from ${APP}.ads_category_order_trade
union
select '$do_date' dt,
       cast(recent_days as BIGINT),
       cast(category_id as STRING),
       category_name,
       cast(sum(if(data_id >= date_add('$do_date', -recent_days + 1), 1, 0)) as BIGINT) order_count,
       cast(count(distinct if(data_id >= date_add('$do_date', -recent_days + 1), user_id, null)) as BIGINT) order_user_count,
       cast(sum(if(data_id >= date_add('$do_date', -recent_days + 1), origin_amount, 0)) as DECIMAL(16, 2))                                                            order_origin_amount
from ${APP}.dwd_trade_order_detail_inc
         lateral view explode(array(1, 7, 30)) tmp as recent_days
where dt > date_sub('$do_date', 30)
group by recent_days, category_id, category_name;
"

ads_subject_order_trade="
insert overwrite table ${APP}.ads_subject_order_trade
select *
from ${APP}.ads_subject_order_trade
union
select '$do_date' dt,
       cast(recent_days as BIGINT),
       cast(subject_id as STRING),
       subject_name,
       cast(sum(if(data_id >= date_add('$do_date', -recent_days + 1), 1, 0)) as BIGINT) order_count,
       cast(count(distinct
                  if(data_id >= date_add('$do_date', -recent_days + 1), user_id, null)) as BIGINT) order_user_count,
       cast(sum(if(data_id >= date_add('$do_date', -recent_days + 1), origin_amount, 0)) as DECIMAL(16, 2))                                                            order_origin_amount
from ${APP}.dwd_trade_order_detail_inc
         lateral view explode(array(1, 7, 30)) tmp as recent_days
where dt > date_sub('$do_date', 30)
group by recent_days, subject_id, subject_name;
"

ads_course_order_trade="
insert overwrite table ${APP}.ads_course_order_trade
select *
from ${APP}.ads_course_order_trade
union
select '$do_date' dt,
       cast(recent_days as BIGINT),
       cast(course_id as STRING),
       course_name,
       cast(sum(if(data_id >= date_add('$do_date', -recent_days + 1), 1, 0)) as BIGINT) order_count,
       cast(count(distinct
                  if(data_id >= date_add('$do_date', -recent_days + 1), user_id, null)) as BIGINT) order_user_count,
       cast(sum(if(data_id >= date_add('$do_date', -recent_days + 1), origin_amount, 0)) as DECIMAL(16, 2))                                                            order_origin_amount
from ${APP}.dwd_trade_order_detail_inc
         lateral view explode(array(1, 7, 30)) tmp as recent_days
where dt > date_sub('$do_date', 30)
group by recent_days, course_id, course_name;
"

ads_course_review="
insert overwrite table ${APP}.ads_course_review
select *
from ${APP}.ads_course_review
union
select '$do_date'   dt,
       cast(recent_days as BIGINT),
       cast(course_id as STRING),
       cast(avg(if(date_id >= date_add('$do_date', -recent_days + 1), review_stars, null)) as DECIMAL(16, 2))  avg_starts,
       count(distinct if(date_id >= date_add('$do_date', -recent_days + 1), user_id, null)) review_user_count,
       concat(cast(count(distinct
                         if(date_id >= date_add('$do_date', -recent_days + 1) and review_stars >= 5, user_id, null)) /
                   count(distinct
                         if(date_id >= date_add('$do_date', -recent_days + 1), user_id, null)) as DECIMAL(16, 2)),'%') good_review_rate
from ${APP}.dwd_behavior_user_course_review_inc
         lateral view explode(array(1, 7, 30)) tmp as recent_days
where dt > date_sub('$do_date', 30)
group by recent_days, course_id;
"

ads_category_retention="
insert overwrite table ${APP}.ads_category_retention
select *
from ${APP}.ads_category_retention
union
select '$do_date' dt,
       7          recent_days,
       cast(t1.category_id as STRING),
       t1.category_name,
       cast(count(distinct t1.user_id) as BIGINT)  first_play_user_count,
       concat(cast(sum(if(t2.order_id is not null and t2.data_id = t1.play_date_first, 1, 0)) /
                   count(distinct t1.user_id) * 100 as DECIMAL(16, 2)), '%') retention_rate
from ${APP}.dws_traffic_user_first_video_play_td t1
         left join ${APP}.dwd_trade_order_detail_inc t2
                   on t1.user_id = t2.user_id
                       and t1.course_id = t2.course_id
                       and t1.dt = '$do_date'
                       and t1.play_date_first between date_sub('$do_date', 6) and '$do_date'
                       and t2.dt between date_sub('$do_date', 6) and '$do_date'
group by t1.category_id, t1.category_name;
"

ads_subject_retention="
insert overwrite table ${APP}.ads_subject_retention
select *
from ${APP}.ads_subject_retention
union
select '$do_date' dt,
       7          recent_days,
       cast(t1.subject_id as STRING),
       t1.subject_name,
       cast(count(distinct t1.user_id) as BIGINT) first_play_user_count,
       concat(cast(sum(if(t2.order_id is not null and t2.data_id = t1.play_date_first, 1, 0)) /
                   count(distinct t1.user_id) * 100 as DECIMAL(16, 2)), '%') retention_rate
from ${APP}.dws_traffic_user_first_video_play_td t1
         left join ${APP}.dwd_trade_order_detail_inc t2
                   on t1.user_id = t2.user_id
                       and t1.course_id = t2.course_id
                       and t1.dt = '$do_date'
                       and t1.play_date_first between date_sub('$do_date', 6) and '$do_date'
                       and t2.dt between date_sub('$do_date', 6) and '$do_date'
group by t1.subject_id, t1.subject_name;
"

ads_course_retention="
insert overwrite table ${APP}.ads_course_retention
select *
from ${APP}.ads_course_retention
union
select '$do_date' dt,
       7          recent_days,
       cast(t1.course_id as STRING),
       t1.course_name,
       cast(count(distinct t1.user_id) as BIGINT) first_play_user_count,
       concat(cast(sum(if(t2.order_id is not null and t2.data_id = t1.play_date_first, 1, 0)) /
                   count(distinct t1.user_id) * 100 as DECIMAL(16, 2)), '%') retention_rate
from ${APP}.dws_traffic_user_first_video_play_td t1
         left join ${APP}.dwd_trade_order_detail_inc t2
                   on t1.user_id = t2.user_id
                       and t1.course_id = t2.course_id
                       and t1.dt = '$do_date'
                       and t1.play_date_first between date_sub('$do_date', 6) and '$do_date'
                       and t2.dt between date_sub('$do_date', 6) and '$do_date'
group by t1.course_id, t1.course_name;
"

ads_trade_stats_tm="
insert overwrite table ${APP}.ads_trade_stats_tm
select * from ${APP}.ads_trade_stats_tm
union
select
    '$do_date' dt,
    recent_days,
    sum(final_amount) total_amount,
    count(distinct(order_id)) order_count,
    count(distinct(user_id)) user_count
from ${APP}.dwd_trade_order_detail_inc lateral view explode(array(1,7,30)) temp as recent_days
where dt > date_sub('$do_date',recent_days)
group by recent_days;
"

ads_trade_by_province="
insert overwrite table ${APP}.ads_trade_by_province
select * from ${APP}.ads_trade_by_province
union
select
    '$do_date' dt,
    recent_days,
    province_id,
    province_name,
    area_code,
    iso_code,
    iso_code_3166_2,
    count(distinct(order_id)) order_count,
    count(distinct(user_id)) user_count,
    sum(final_amount) total_amount
from ${APP}.dwd_trade_order_detail_inc lateral view explode(array(1,7,30)) temp as recent_days
where dt > date_sub('$do_date',recent_days)
group by recent_days,province_id,province_name,area_code,iso_code,iso_code_3166_2;
"

ads_test_exam_by_page="
insert overwrite table ${APP}.ads_test_exam_by_page
select * from ${APP}.ads_test_exam_by_page
union
select
    '$do_date' dt,
    recent_days,
    paper_id,
    paper_title,
    cast(avg(score) as decimal(16,2)),
    cast(avg(duration_sec) as decimal(16,2)),
    sum(distinct(user_id))
from ${APP}.dwd_behavior_user_test_exam_inc lateral view explode(array(1,7,30)) temp as recent_days
where dt > date_sub('$do_date',recent_days)
group by recent_days,paper_id,paper_title;
"

ads_test_exam_by_course="
insert overwrite table ${APP}.ads_test_exam_by_course
select * from ${APP}.ads_test_exam_by_course
union
select
    dt,
    recent_days,
    c1.course_id,
    c2.course_name,
    avg_score,
    avg_time,
    user_count
from(
    select
        '$do_date' dt,
        recent_days,
        course_id,
        cast(avg(score) as decimal(16,2)) avg_score,
        cast(avg(duration_sec) as decimal(16,2)) avg_time,
        sum(distinct(user_id)) user_count
    from ${APP}.dwd_behavior_user_test_exam_inc lateral view explode(array(1,7,30)) temp as recent_days
    where dt > date_sub('$do_date',recent_days)
    group by recent_days,course_id
        )c1
left join
    (select
        course_id,course_name
    from ${APP}.dim_course_subject_category_full)c2
    on c1.course_id = c2. course_id;
"

ads_exam_stats_by_page_score="
insert overwrite table ${APP}.ads_exam_stats_by_page_score
select * from ${APP}.ads_exam_stats_by_page_score
union
select
    '$do_date' dt,
    recent_days,
    paper_id,
    paper_title,
    concat_ws('~',cast(cast( (score/10) as INT) * 10 as STRING),cast((cast( (score/10) as INT) * 10 + 10) as STRING)),
    sum(distinct(user_id))
from ${APP}.dwd_behavior_user_test_exam_inc lateral view explode(array(1,7,30)) temp as recent_days
where dt > date_sub('$do_date',recent_days)
group by recent_days,paper_id,cast( (score/10) as INT) * 10,paper_title;
"

ads_correct_rate_by_question="
insert overwrite table ${APP}.ads_correct_rate_by_question
select * from ${APP}.ads_correct_rate_by_question
union
select
    '$do_date' dt,
    recent_days,
    paper_id,
    paper_title,
    question_id,
    question_type,
    question_txt,
    sum(is_correct) correct_count,
    count(*) total_count,
    cast(((sum(is_correct) / count(*)) * 100) as DECIMAL(16, 2))
from ${APP}.dwd_behavior_user_test_exam_question_inc lateral view explode(array(1,7,30)) temp as recent_days
where dt > date_sub('$do_date',recent_days)
group by recent_days,paper_id,paper_title,question_id,question_type,question_txt;
"

ads_video_by_chapter="
insert overwrite table ${APP}.ads_video_by_chapter
select * from ${APP}.ads_video_by_chapter
union
select
    '$do_date',
    recent_days,
    course_id,
    chapter_id,
    video_id,
    nvl(count(video_id),0),
    nvl(sum(play_sec) / count(distinct user_id),0),
    nvl(count(distinct user_id),0)
from ${APP}.dwd_traffic_video_play_inc
lateral view explode (array(1,7,30)) tmp as recent_days
where  dt >= date_sub('$do_date',recent_days)
group by course_id, chapter_id ,video_id,recent_days;
"

ads_video_by_course="
insert overwrite table ${APP}.ads_video_by_course
select * from ${APP}.ads_video_by_course
union
select
    '$do_date',
    recent_days,
    course_id,
    video_id,
    nvl(count(video_id),0),
    nvl(sum(play_sec) / count(distinct user_id),0),
    nvl(count(distinct user_id),0)
from ${APP}.dwd_traffic_video_play_inc
lateral view explode (array(1,7,30)) tmp as recent_days
where  dt >= date_sub('$do_date',recent_days)
group by course_id ,video_id,recent_days;
"

ads_finish_video_by_course="
insert overwrite table ${APP}.ads_finish_video_by_course
select * from ${APP}.ads_finish_video_by_course
union
select
    '$do_date',
    recent_days,
    course_id,
    sum(if(numbers > 1,1,0)),
    sum(numbers)
from (
     select
    recent_days,
    course_id,
    if((sum(if(play_sec_rate > 90,1,0))) =
    (count(video_id)),1,0) numbers
from ${APP}.dws_traffic_user_video_finish_1d
lateral view explode (array(1,7,30)) tmp as recent_days
where  dt >= date_sub('$do_date',recent_days)
group by recent_days,course_id,user_id
         )dtuvf1dt
group by recent_days,course_id;
"

ads_finish_video_stat="
insert overwrite table ${APP}.ads_finish_video_stat
select * from ${APP}.ads_finish_video_stat
union
select
    '$do_date',
    recent_days,
    sum(finish_video_numbers),
    sum(finish_video_counts)
from ${APP}.ads_finish_video_by_course
group by recent_days;
"

ads_finish_video_by_chapter="
insert overwrite table ${APP}.ads_finish_video_by_chapter
select * from ${APP}.ads_finish_video_by_chapter
union
    select '$do_date',
    recent_days,
    course_id,
    nvl(sum(if(finish_play_people > 1,1,finish_play_people)) / finish_play_people
        ,0)
from (
     select
    recent_days,
    course_id,
    sum(if(play_sec_rate > 90,1,0)) finish_play_people
from ${APP}.dws_traffic_user_video_finish_1d
lateral view explode (array(1,7,30)) tmp as recent_days
where  dt >= date_sub('$do_date',recent_days)
group by recent_days,course_id,user_id
         ) dtuvf1dt
group by recent_days, course_id ,finish_play_people;
"


case $1 in
"ads_traffic_stats_by_channel")
hive -e "$ads_traffic_stats_by_channel"
;;
"ads_page_path")
hive -e "$ads_page_path"
;;
"ads_order_by_channel")
hive -e "$ads_order_by_channel"
;;
"ads_user_change")
hive -e "$ads_user_change"
;;
"ads_user_retention")
hive -e "$ads_user_retention"
;;
"ads_user_count")
hive -e "$ads_user_count"
;;
"ads_user_action")
hive -e "$ads_user_action"
;;
"ads_new_order_user_stats")
hive -e "$ads_new_order_user_stats"
;;
"ads_age_state_order")
hive -e "$ads_age_state_order"
;;
"ads_category_order_trade")
hive -e "$ads_category_order_trade"
;;
"ads_subject_order_trade")
hive -e "$ads_subject_order_trade"
;;
"ads_course_order_trade")
hive -e "$ads_course_order_trade"
;;
"ads_course_review")
hive -e "$ads_course_review"
;;
"ads_category_retention")
hive -e "$ads_category_retention"
;;
"ads_subject_retention")
hive -e "$ads_subject_retention"
;;
"ads_course_retention")
hive -e "$ads_course_retention"
;;
"ads_trade_stats_tm")
hive -e "$ads_trade_stats_tm"
;;
"ads_trade_by_province")
hive -e "$ads_trade_by_province"
;;
"ads_test_exam_by_page")
hive -e "$ads_test_exam_by_page"
;;
"ads_test_exam_by_course")
hive -e "$ads_test_exam_by_course"
;;
"ads_exam_stats_by_page_score")
hive -e "$ads_exam_stats_by_page_score"
;;
"ads_correct_rate_by_question")
hive -e "$ads_correct_rate_by_question"
;;
"ads_video_by_chapter")
hive -e "$ads_video_by_chapter"
;;
"ads_video_by_course")
hive -e "$ads_video_by_course"
;;
"ads_finish_video_by_course")
hive -e "$ads_finish_video_by_course"
;;
"ads_finish_video_stat")
hive -e "$ads_finish_video_stat"
;;
"ads_finish_video_by_chapter")
hive -e "$ads_finish_video_by_chapter"
;;
"all")
hive -e "$ads_traffic_stats_by_channel$ads_page_path$ads_order_by_channel$ads_user_change$ads_user_retention$ads_user_count$ads_user_action$ads_new_order_user_stats$ads_age_state_order$ads_category_order_trade$ads_subject_order_trade$ads_course_order_trade$ads_course_review$ads_category_retention$ads_subject_retention$ads_course_retention$ads_trade_stats_tm$ads_trade_by_province$ads_test_exam_by_page$ads_test_exam_by_course$ads_exam_stats_by_page_score$ads_correct_rate_by_question$ads_video_by_chapter$ads_video_by_course$ads_finish_video_by_course$ads_finish_video_stat$ads_finish_video_by_chapter"
;;
esac

